package org.hyl.javabase.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;

public class demo1 {
	public static void main(String[] args) {
		fun8();
	}

	public static void fun1() {
		Connection con = null;// ����һ�����ݿ�����
		PreparedStatement pre = null;// ����Ԥ����������һ�㶼�������������Statement
		ResultSet result = null;// ����һ�����������
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");// ����Oracle��������
			System.out.println("��ʼ�����������ݿ⣡");
			String url = "jdbc:oracle:thin:@192.168.1.7:1521:orcl";// 127.0.0.1�Ǳ�����ַ��XE�Ǿ����Oracle��Ĭ�����ݿ���
			String user = "scott";// �û���,ϵͳĬ�ϵ��˻���
			String password = "123";// �㰲װʱѡ���õ�����
			con = DriverManager.getConnection(url, user, password);// ��ȡ����
			System.out.println("���ӳɹ���");
			String sql = "select * from temp1 ";// Ԥ������䣬�������������
			pre = con.prepareStatement(sql);// ʵ����Ԥ�������
			//pre.setString(1, "%x%");// ���ò�����ǰ���1��ʾ�����������������Ǳ�������������
			result = pre.executeQuery();// ִ�в�ѯ��ע�������в���Ҫ�ټӲ���
			
			while (result.next()){
				// ���������Ϊ��ʱ
				System.out.println("ѧ��:" + result.getInt("id") 
				+ "����:" + result.getString("name")
				+":"+result.getNString("name"));
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				// ��һ������ļ�������رգ���Ϊ���رյĻ���Ӱ�����ܡ�����ռ����Դ
				// ע��رյ�˳�����ʹ�õ����ȹر�
				if (result != null)
					result.close();
				if (pre != null)
					pre.close();
				if (con != null)
					con.close();
				System.out.println("���ݿ������ѹرգ�");
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	public static void fun2() {
		Connection oracle_conn = null;
		Statement oracle_stmt = null;
		Statement oracle_stmt2 = null;
		ResultSet oracle_rs = null;
		ResultSet oracle_rs2 = null;

		try {
			String url = "jdbc:oracle:thin:@192.168.1.7:1521:orcl";
			Class.forName("oracle.jdbc.driver.OracleDriver");
			oracle_conn = DriverManager.getConnection(url, "scott", "123");
			
			oracle_stmt = oracle_conn.createStatement();
			oracle_stmt2 = oracle_conn.createStatement();
			oracle_rs2 = oracle_stmt2.executeQuery("select * from temp1");
			int id=0;
			String name=null;
			while (oracle_rs2.next()) {
				 id=oracle_rs2.getInt("id");
				 name=oracle_rs2.getString("name");
				System.out.println("���ڲ���Id����" + id + "��¼...");
				
				oracle_stmt.executeUpdate("insert into temp2 values(" 
						+ (id+100) + ",'"+ name+ "')");
			}

		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (oracle_rs != null) {
					oracle_rs.close();
					oracle_rs = null;
				}

				if (oracle_stmt != null) {
					oracle_stmt.close();
					oracle_stmt = null;
				}

				if (oracle_conn != null) {
					oracle_conn.close();
					oracle_conn = null;
				}

				if (oracle_rs2 != null) {
					oracle_rs2.close();
					oracle_rs2 = null;
				}

			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public static void fun3() {
		Connection oracle_conn = null;
		PreparedStatement oracle_stmt = null;
		Statement oracle_stmt1 = null;
		ResultSet oracle_rs = null;
		ResultSet rs2 = null;

		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			String url = "jdbc:oracle:thin:@192.168.1.7:1521:orcl";
			oracle_conn = DriverManager.getConnection(url, "scott", "123");
			oracle_stmt = oracle_conn.prepareStatement(
					"insert into temp2(id,name) values(?, ?)");
			oracle_stmt1 = oracle_conn.createStatement();
			rs2 = oracle_stmt1.executeQuery("select * from temp1");

			while (rs2.next()) {
				System.out.println("���ڲ���Id����" + rs2.getInt("id") + "��¼...");
				oracle_stmt.setInt(1, (rs2.getInt("id")+1000));
				oracle_stmt.setString(2, rs2.getString("name"));
				oracle_stmt.executeUpdate();
			}
			System.out.println("�������ݵ�Video_ItemInfo���в�������ɣ�");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (oracle_rs != null) {
					oracle_rs.close();
					oracle_rs = null;
				}

				if (oracle_stmt != null) {
					oracle_stmt.close();
					oracle_stmt = null;
				}

				if (oracle_conn != null) {
					oracle_conn.close();
					oracle_conn = null;
				}

				if (rs2 != null) {
					rs2.close();
					rs2 = null;
				}

			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public static void fun10() {

		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			String url = "jdbc:oracle:thin:@192.168.1.7:1521:orcl";
			Connection conn = DriverManager.getConnection(url, "scott", "123");
			CallableStatement cstmt = conn.prepareCall("{call pinsert_1(?, ?)}");
			cstmt.registerOutParameter(2, Types.INTEGER);
			cstmt.setInt(1, 3);
			cstmt.execute();
			System.out.println(cstmt.getInt(2));
			
			cstmt.setInt(1, 4);
			cstmt.execute();
			System.out.println(cstmt.getInt(2));
			
			cstmt.setInt(1, 5);
			cstmt.execute();
			System.out.println(cstmt.getInt(2));
			cstmt.close();
			conn.close();
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
	}

	public static void fun5() {
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			String url = "jdbc:oracle:thin:@192.168.1.7:1521:orcl";
			Connection conn = DriverManager.getConnection(url, "scott", "123");
			/*
			 * Statement stmt = conn.createStatement(); 
			 * stmt.addBatch("insert into dept2 values (51, '500', 'haha')");
			 * stmt.addBatch("insert into dept2 values (52, '500', 'haha')");
			 * stmt.addBatch("insert into dept2 values (53, '500', 'haha')"); 
			 * stmt.executeBatch();
			 * stmt.close();
			 */
			PreparedStatement ps = conn.prepareStatement("insert into temp2 values (?, ?)");
			ps.setInt(1, 10);
			ps.setString(2, "haha");
			ps.addBatch();
			ps.setInt(1, 11);
			ps.setString(2, "haha");
			ps.addBatch();
			ps.executeBatch();
			ps.close();
			conn.close();
		} catch (Exception e) {
			// TODO: handle exception
		}

	}

	public static void fun6() {

		Connection conn = null;
		Statement stmt = null;

		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			String url = "jdbc:oracle:thin:@192.168.1.7:1521:orcl";
			conn = DriverManager.getConnection(url, "scott", "123");
			conn.setAutoCommit(false);
			stmt = conn.createStatement();
			stmt.addBatch("insert into temp2 values (11, '500')");
			stmt.addBatch("insert into temp2 values (12, '500')");
			stmt.addBatch("insert into temp2 values (13, '500')");
			stmt.executeBatch();
			conn.commit();
			conn.setAutoCommit(true);
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {

			e.printStackTrace();

			try {
				if (conn != null) {
					conn.rollback();
					conn.setAutoCommit(true);
				}
			} catch (SQLException e1) {
				e1.printStackTrace();
			}
		} finally {
			try {
				if (stmt != null)
					stmt.close();
				if (conn != null)
					conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

	}
	 public static void fun7() {  
		  
	        try {  
	         //   
	        
	        	String url = "jdbc:oracle:thin:@192.168.1.7:1521:orcl";
	        	Connection conn = DriverManager.getConnection(url, "scott", "123");
	            Statement stmt = conn.createStatement(  
	                    ResultSet.TYPE_SCROLL_INSENSITIVE,  
	                    ResultSet.CONCUR_READ_ONLY);  
	            ResultSet rs = stmt  
	                    .executeQuery("select * from emp order by sal");  
	            rs.next();  
	            System.out.println(rs.getInt(1));  
	            rs.last();  
	            System.out.println(rs.getString(1));  
	            System.out.println(rs.isLast());  
	            System.out.println(rs.isAfterLast());  
	            System.out.println(rs.getRow());  
	            rs.previous();  
	            System.out.println(rs.getString(1));  
	            rs.absolute(6);  
	            System.out.println(rs.getString(1));  
	            rs.close();  
	            stmt.close();  
	            conn.close();  
	        } catch (SQLException e) {  
	            e.printStackTrace();  
	        }  
	    }  
	 
	  public static void fun8(){  
	      
		    try{  
		       
		    	String url = "jdbc:oracle:thin:@192.168.1.7:1521:orcl";
	        	Connection conn = DriverManager.getConnection(url, "scott", "123");
		        Statement stmt = conn.createStatement(
		        		ResultSet.TYPE_SCROLL_INSENSITIVE
		        		,ResultSet.CONCUR_UPDATABLE);     
		        ResultSet rs=stmt.executeQuery("select empno,ename,mgr,sal from emp2 ");  
		          
		        rs.next();  
		        //����һ������  
		        rs.updateString("ename","AAAA");  
		        rs.updateRow();  
		  
		        //��������  
		        rs.moveToInsertRow();  
		        rs.updateInt(1, 9999);  
		        rs.updateString("ename","AAAA");  
		        rs.updateInt("mgr", 7839);  
		        rs.updateDouble("sal", 99.99);  
		        rs.insertRow();  
		        //������ƶ����½�����  
		        rs.moveToCurrentRow();  
		  
		        //ɾ����  
		        rs.absolute(5);  
		        rs.deleteRow();  
		        rs.close();  
	            stmt.close();  
	            conn.close();  
		        //ȡ������  
		        //rs.cancelRowUpdates();  
		  
		      }catch(SQLException e){  
		        e.printStackTrace();  
		      }  
		    }  

}
